﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Data;
using System.Xml;
using SdmxMl.SmsImportCfg;
using SdmxMl.Common;
using SdmxMl.Structure;
using SdmxMl.Manager;
using SdmxMl.GenericMetadata;
namespace SakeVar
{
    public class ModuleVarImport
    {
        #region Constants

        const int COL_LEVEL = 1;
        const int COL_CODE = 2;
        const int COL_NAME_DE = 3;
        const int COL_NAME_FR = 4;
        const int COL_LENGTH = 5;
        const int COL_VAGUE = 10;
        const int COL_GROUP = 11;
        const int COL_REM_TECH = 13;
        const int COL_QUEST_PRINT = 15;

        #endregion

        private ExcelDataReader.ExcelDataReader spreadsheetConcept = null;
        private ConceptSchemeType cst;

        public ModuleVarImport(string excelPath)
        {
            spreadsheetConcept = LoadSources(excelPath);
            int theYear = 2013;

            cst = new ConceptSchemeType();
            cst.Id = "CS_SAKE_MOD_EU";
            cst.Name.UpdateForLanguage("de", "EU-Modul «Arbeitsunfälle und sonstige berufsbedingte Gesundheitsprobleme»");
            cst.Name.UpdateForLanguage("fr", "Module EU «Accidents du travail et autres problèmes de santé liés au travail»");
            cst.AgencyId = "CH1_SAKE";
            cst.Version = theYear.ToString() + ".0";

        }

        #region Excel Loader

        private ExcelDataReader.ExcelDataReader LoadSources(string propertyPath)
        {
            ExcelDataReader.ExcelDataReader spreadsheet = null;
            if (propertyPath == null)
                throw new ApplicationException("Please provides path.");

            FileStream fs = null;
            try
            {
                fs = new FileStream(propertyPath, FileMode.Open, FileAccess.Read, FileShare.Read);
                spreadsheet = new ExcelDataReader.ExcelDataReader(fs);
            }
            finally
            {
                if (fs != null)
                    fs.Close();
            }

            return spreadsheet;
        }
        #endregion

        public void ConstructVars(int sheetIndex)
        {
            Annotation a = null;

            string[] parents = new string[5];
            for (int i = 0; i < parents.Length; i++)
                parents[i] = string.Empty;

            // List of VarProto, independant of year
            // Must be located unique worksheet
            DataTable dt = spreadsheetConcept.WorkbookData.Tables[sheetIndex];
            WorkSheetHelper wh = new WorkSheetHelper(dt);


            for (int row = 1; row <= dt.Rows.Count; row++)
            {
                if (row > 1) // 1 header rows
                {
                    ConceptType c = new ConceptType();
                    if ((c.Id = wh.GetCellContent(row, COL_CODE)).Length == 0)
                        break;
                    
                    cst.ConceptList.Add(c);

                    c.Name.UpdateForLanguage(LangSupport.GERMAN_ID, wh.GetCellContent(row, COL_NAME_DE));
                    c.Name.UpdateForLanguage(LangSupport.FRENCH_ID, wh.GetCellContent(row, COL_NAME_FR));

                    string vague = wh.GetCellContent(row, COL_VAGUE);
                    if (vague.Length > 0)
                    {
                        a = new Annotation("VAGUE");
                        a.Title = vague;
                        c.AnnotationList.Add(a);
                        c.TextFormat.TextType = TextTypeType.Integer;
                        c.TextFormat.maxLength = wh.GetCellContent(row, COL_LENGTH);
                    }

                    string group = wh.GetCellContent(row, COL_GROUP);
                    if (group.Length > 0)
                    {
                        a = new Annotation("GROUP");
                        a.Title = group;
                        c.AnnotationList.Add(a);
                    }

                    string quest = wh.GetCellContent(row, COL_QUEST_PRINT);
                    if (quest.Length > 0)
                    {
                        a = new Annotation("QUEST_PRINT");
                        a.Title = quest;
                        c.AnnotationList.Add(a);
                    }

                    string rem = wh.GetCellContent(row, COL_REM_TECH);
                    if (rem.Length > 0)
                    {
                        a = new Annotation("REMTECH");
                        a.Title = rem;
                        c.AnnotationList.Add(a);
                    }

                    int level = int.Parse(wh.GetCellContent(row, COL_LEVEL));
                    parents[level] = c.Id;
                    c.Parent = parents[level-1];
                }
            }
            // Save
            SmManager sm = new SmManager();
            sm.PutArtefact(SdmxArtefactType.Concepts, cst);
            XmlDocument xDoc = sm.GetAsDocument();
            xDoc.Save(@"C:\doc\" + cst.FullIdent + ".xml");

        }
    }
}
